﻿using System.Data;
using System.Data.OleDb;

namespace CLM.Gestao.Importacao
{
    public class Excel
    {
        private OleDbConnection ExcelConnection;

        private string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Mode=Share Deny Write;Extended Properties=\"HDR=Yes;IMEX=1;ImportMixedTypes=Text;\";Jet OLEDB:Engine Type=37";

        private void OpenConnection(string pFile)
        {
            ExcelConnection = new OleDbConnection(string.Format(ConnectionString, @pFile));

            ExcelConnection.Open();
        }

        private void CloseConnection()
        {
            ExcelConnection.Close();
        }

        public DataSet ExecuteRecordset(string pFile, string pQuery)
        {
            OpenConnection(pFile);

            OleDbCommand cmd = new OleDbCommand(pQuery, ExcelConnection);
            OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            adpt.Fill(ds);

            CloseConnection();
            return ds;
        }
    }
}